package com.eleadmin.ocean.service.impl;

import cn.hutool.core.collection.CollUtil;
import com.eleadmin.common.core.config.ConfigProperties;
import com.eleadmin.common.core.utils.FileServerUtil;
import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.common.system.entity.User;
import com.eleadmin.damon.service.SequenceService;
import com.eleadmin.damon.utils.results.PageResult2;
import com.eleadmin.damon.utils.utils.DateUtil;
import com.eleadmin.damon.utils.utils.StringUtil;
import com.eleadmin.damon.utils.utils.Utils;
import com.eleadmin.ocean.biz.OceanBulkCargoFeeBiz;
import com.eleadmin.ocean.biz.OceanBulkCargoProcessBiz;
import com.eleadmin.ocean.entity.OceanBulkCargo;
import com.eleadmin.ocean.entity.OceanBulkCargoFee;
import com.eleadmin.ocean.entity.OceanBulkCargoProcess;
import com.eleadmin.ocean.param.OceanBulkCargoParam;
import com.eleadmin.ocean.service.OceanBulkCargoService;
import com.eleadmin.order.biz.CustomerBiz;
import com.eleadmin.order.entity.*;
import com.eleadmin.order.param.ContainerBillParam;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import com.eleadmin.ocean.biz.OceanBulkCargoBiz;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * 海运散货服务实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2023-10-25 15:36:44
 */
@Service
public class OceanBulkCargoServiceImpl /*extends ServiceImpl<OceanBulkCargoMapper, OceanBulkCargo> */ implements OceanBulkCargoService {

    @Autowired
    private OceanBulkCargoBiz oceanBulkCargoBiz;
    @Autowired
    private OceanBulkCargoFeeBiz oceanBulkCargoFeeBiz;
    @Autowired
    private OceanBulkCargoProcessBiz oceanBulkCargoProcessBiz;
    @Autowired
    private CustomerBiz customerBiz;
    @Autowired
    private SequenceService sequenceService;

    @Override
    public JsonResult list(PageResult2<T> page, OceanBulkCargoParam oceanBulkCargoParam, User loginUser) throws Exception {
        return oceanBulkCargoBiz.list(page, oceanBulkCargoParam, loginUser);
    }

    @Override
    public JsonResult list(OceanBulkCargoParam oceanBulkCargoParam, User loginUser) {
        return JsonResult.ok().setData(oceanBulkCargoBiz.list(oceanBulkCargoParam, loginUser));
    }

    @Override
    public JsonResult getById(String id) {
        return JsonResult.ok().setData(oceanBulkCargoBiz.getById(id));
    }

    @Transactional
    @Override
    public JsonResult save(OceanBulkCargo oceanBulkCargo, User loginUser) {
        String bh = sequenceService.genCertCode2("OBC", "11", 9999);
        oceanBulkCargo.setBillCode(bh);
//        oceanBulkCargo.setBillCode(UUID.randomUUID().toString());
        Customer customer = customerBiz.getById(oceanBulkCargo.getCustomerId() + "");
        oceanBulkCargo.setSalesmanId(customer.getSalesmanid());
        oceanBulkCargo.setSalesman(customer.getSalesman());
        oceanBulkCargo.setOperaterId(customer.getOperaterid());
        oceanBulkCargo.setOperater(customer.getOperater());
        int id = oceanBulkCargoBiz.save(oceanBulkCargo, loginUser);

        //进程表添加下单
        OceanBulkCargoProcess oceanBulkCargoProcess = new OceanBulkCargoProcess();
        oceanBulkCargoProcess.setBulkCargoId(id + "");
        oceanBulkCargoProcess.setOperateDate(DateUtil.getCurrentDate());
        oceanBulkCargoProcess.setState("下单");

        oceanBulkCargoProcessBiz.save(oceanBulkCargoProcess, loginUser);
        return JsonResult.ok("添加成功");

    }

    @Transactional
    @Override
    public JsonResult update(OceanBulkCargo oceanBulkCargo, User loginUser) {
        if (oceanBulkCargoBiz.update(oceanBulkCargo, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Transactional
    @Override
    public JsonResult remove(String id, User loginUser) {
        if (oceanBulkCargoBiz.remove(id, loginUser)) {
            return JsonResult.ok("删除成功");
        }
        return JsonResult.error("删除失败");
    }


    //应收应付分类导出财务报表
    @Override
    public JsonResult exportsFinance(OceanBulkCargo oceanBulkCargo, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception {
        String fileName = "oceanBulkCargoFinance.xlsx";
        File file = new File(getUploadDir(), fileName);
        if (file.exists()) {
            file.delete();
        }
        oceanBulkCargo.setDate1(oceanBulkCargo.getDate1().substring(0, 10) + " 00:00");
        oceanBulkCargo.setDate2(oceanBulkCargo.getDate2().substring(0, 10) + " 23:59");
        List<OceanBulkCargo> oceanBulkCargoList = getOceanBulkCargoList(oceanBulkCargo, loginUser);
        if (oceanBulkCargoList != null && oceanBulkCargoList.size() > 0) {
            List<String> feeNames = oceanBulkCargoFeeBiz.feeNamesForCustomer(oceanBulkCargo, loginUser);
            if (feeNames != null && feeNames.size() > 0) {
                exportsFinanceoceanBulkCargoExcel(oceanBulkCargoList, oceanBulkCargo, fileName);
                FileServerUtil.preview(file, true, fileName, null, null, response, request);
            } else {
                try {
                    response.setContentType("text/html; charset=UTF-8");
                    response.getWriter().println("没有对应的数据哦,请再次查询!");
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return JsonResult.ok("文件已导出");
    }

    public List<OceanBulkCargo> getOceanBulkCargoList(OceanBulkCargo oceanBulkCargo, User loginUser) {
        OceanBulkCargoParam oceanBulkCargoParam = new OceanBulkCargoParam();
        oceanBulkCargoParam.setFactoryPackDateSearch1(oceanBulkCargo.getDate1());
        oceanBulkCargoParam.setFactoryPackDateSearch2(oceanBulkCargo.getDate2());
        oceanBulkCargoParam.setCustomerEq(oceanBulkCargo.getCustomer());
        List<OceanBulkCargo> oceanBulkCargoList = oceanBulkCargoBiz.list(oceanBulkCargoParam, loginUser);
        if (oceanBulkCargoList != null && oceanBulkCargoList.size() > 0) {
            for (int i = 0; i < oceanBulkCargoList.size(); i++) {
                OceanBulkCargo oceanBulkCargo2 = oceanBulkCargoList.get(i);
                oceanBulkCargoParam = new OceanBulkCargoParam();
//                if (StringUtil.isNotBlank(containerBill.getType())) {
//                    containerBillParam.setType(containerBill.getType());//导出应收应付
//                }
                if (StringUtil.isNotBlank(oceanBulkCargo2.getId())) {
                    oceanBulkCargoParam.setId(oceanBulkCargo2.getId());
                }
//                if (StringUtil.isNotBlank(containerBill.getCustomerAbbreviation())) {
//                    containerBillParam.setCustomerAbbreviation(containerBill.getCustomerAbbreviation());
//                }
                List<OceanBulkCargoFee> oceanBulkCargoFeeList = oceanBulkCargoFeeBiz.listForCustomerExport(oceanBulkCargoParam, loginUser);
                oceanBulkCargo2.setOceanBulkCargoFees(oceanBulkCargoFeeList);
            }
        }
        return oceanBulkCargoList;
    }

    public void exportsFinanceoceanBulkCargoExcel(List<OceanBulkCargo> oceanBulkCargoList, OceanBulkCargo oceanBulkCargo, String fileName) throws Exception {
        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(oceanBulkCargo.getDate2())) {
            date2 = " 到 " + oceanBulkCargo.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期：" + oceanBulkCargo.getDate1().substring(0, 10) + date2); //设置单元格内容
        List<String> feeNamesys = new ArrayList<>();
        feeNamesys.add("运费");
        feeNamesys.add("国内杂费");
        feeNamesys.add("国外杂费");
        feeNamesys.add("后端派送费");
        feeNamesys.add("关税");
        List<String> feeNamesyf = new ArrayList<>();
        feeNamesys.add("运费");
        feeNamesys.add("国内杂费");
        feeNamesys.add("国外杂费");
        feeNamesys.add("后端派送费");
        feeNamesys.add("关税");
        List<String> headRow = CollUtil.newArrayList("序号", "日期", "客户简称", "单号", "客户单号");
        headRow.addAll(feeNamesys);
        headRow.remove(headRow.size() - 1);
        headRow.add("应收合计");
        headRow.add("应付明细");
        headRow.addAll(feeNamesyf);
        headRow.remove(headRow.size() - 1);
        headRow.add("应付合计");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色

        headRow = CollUtil.newArrayList("序号", "日期", "客户简称", "单号", "客户单号");
        headRow.addAll(feeNamesys);
        headRow.add("应收合计");
        headRow.addAll(feeNamesyf);
        headRow.add("应付合计");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(4);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
//        xssfSheet.createFreezePane( 0, 5, 0, 0);//固定列

        String[] letters = new String[]{"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
                "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR",
                "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ",
                "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB",
                "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT",
                "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL",
                "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ"};
//
//        //把List里面的数据写到excel中
//        for (int i = 0; i < containerBillList.size(); i++) {
//            ContainerBill bill = containerBillList.get(i);
//            List<ContainerBillFee> billFees = bill.getContainerBillFeeList();
//
//            xssfRow = xssfSheet.createRow(i + 5);
//            //创建每个单元格Cell，即列的数据
//            xssfCell = xssfRow.createCell(0); //创建单元格
//            xssfCell.setCellValue(i + 1); //设置单元格内容
//            xssfCell = xssfRow.createCell(1); //创建单元格
//            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容
//
//            String add = "";
//            if (bill.getFactoryAddress() != null && bill.getFactoryAddress().length() > 0) {
//                JSONArray jsonArray = JSONArray.fromObject(bill.getFactoryAddress());
//                for (int k=0; k<jsonArray.size(); k++) {
//                    if(k != 0) {
//                        add += ", \n";
//                    }
//                    JSONObject jsonObject = jsonArray.getJSONObject(k);
//                    add += jsonObject.getString("factoryAddress").replaceAll("\t", "");
//                }
//            }
//
//            List<String> row = CollUtil.newArrayList((i + 1) + "", com.eleadmin.damon.utils.utils.DateUtil.formatDate(com.eleadmin.damon.utils.utils.DateUtil.parseDate(bill.getFactoryPackDate() + ":00"), "MM-dd"),
//                    bill.getCustomer(), bill.getBillOfLading(), bill.getBoxType(),
//                    bill.getBoxNum(), bill.getSealno(), add,
//                    bill.getUpYard() == null ? "/" : bill.getUpYard(), bill.getArrivalPort() == null ? "/" : bill.getArrivalPort(),
//                    bill.getCarTeam(), bill.getDriver(), bill.getDriverPhone(), bill.getCarNo(),
//                    bill.getDock(), bill.getSealedLand(), bill.getOperater(), bill.getSalesman()
//            );
//            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//            fontStyle = xssfWorkbook.createFont(); // 字体样式
//            fontStyle.setBold(false); // 加粗
//            fontStyle.setFontName("宋体"); // 字体
//            fontStyle.setFontHeightInPoints((short) 10); // 大小
//            // 将字体样式添加到单元格样式中
//            cellStyle.setFont(fontStyle);
//            cellStyle.setAlignment(HorizontalAlignment.CENTER);
//            cellStyle.setBorderBottom(BorderStyle.THIN);
//            cellStyle.setBorderLeft(BorderStyle.THIN);
//            cellStyle.setBorderRight(BorderStyle.THIN);
//            cellStyle.setBorderTop(BorderStyle.THIN);
//
////            float temp_ret = 0;
////            for(int t = 0; t < feeNamesys.size(); t ++) {
////                String feeName = feeNamesys.get(t);
////                temp_ret = 0;
////                for (int j = 0; j < billFees.size(); j ++) {
////                    BillFee bf = billFees.get(j);
////                    if(bf.getType().equals("应收") && bf.getFeeName().equals(feeName)) {
////                        if(Utils.isNotEmpty(bf.getPrice())) {
////                            temp_ret += Float.parseFloat(bf.getPrice());
////                        }
////                    }
////                }
////                row.add(temp_ret + "");
////            }
//            float temp_ret = 0;
//            for (int t = 0; t < feeNamesys.size(); t++) {
//                String feeName = feeNamesys.get(t);
//                temp_ret = 0;
//                for (int j = 0; j < billFees.size(); j++) {
//                    ContainerBillFee bf = billFees.get(j);
//                    if (bf.getType().equals("应收") && bf.getFeeModules().equals(feeName)) {
//                        if (Utils.isNotEmpty(bf.getPrice())) {
//                            temp_ret += Float.parseFloat(bf.getPrice());
//                        }
//                    }
//                }
//                row.add(temp_ret + "");
//            }
//            row.add("function::SUM(" + letters[8] + (i + 6) + ":" + letters[feeNamesys.size() + 7] + (i + 6) + ")");
//
////            float cwbx = 0,ygt = 0;
////            for(int t = 0; t < feeNamesyf.size(); t ++) {
////                cwbx = 0;
////                ygt = 0;
////                temp_ret = 0;
////                String feeName = feeNamesyf.get(t);
////                for (int j = 0; j < billFees.size(); j ++) {
////                    BillFee bf = billFees.get(j);
////                    if(Utils.isNotEmpty(bf.getPrice())) {
////                        if (bf.getType().equals("应付")) {
////                            if (bf.getFeeName().equals(feeName) && !bf.getInvoicename().equals("易港通")
////                                    && !bf.getInvoicename().equals("财务报销")) {
////                                temp_ret += Float.parseFloat(bf.getPrice());
////                            } else if (bf.getInvoicename().equals("易港通")) {
////                                ygt += Float.parseFloat(bf.getPrice());
////                            } else if (bf.getInvoicename().equals("财务报销")) {
////                                cwbx += Float.parseFloat(bf.getPrice());
////                            }
////                        }
////                    }
////                }
////                row.add(temp_ret + "");
////            }
////            row.add(cwbx+"");
////            row.add(ygt+"");
//            for (int t = 0; t < feeNamesyf.size(); t++) {
//                temp_ret = 0;
//                String feeName = feeNamesyf.get(t);
//                for (int j = 0; j < billFees.size(); j++) {
//                    ContainerBillFee bf = billFees.get(j);
//
//                    if (bf.getType().equals("应付") && bf.getFeeModules().equals(feeName)) {
//                        if (Utils.isNotEmpty(bf.getPrice())) {
//                            temp_ret += Float.parseFloat(bf.getPrice());
//                        }
//                    }
//                }
//                row.add(temp_ret + "");
//            }
//            row.add("function::SUM(" + letters[feeNamesys.size() + 9] + (i + 6) + ":" + letters[feeNamesys.size() + feeNamesyf.size() + 8] + (i + 6) + ")");
//            float ysTotalNoTax = 0, yfTotalNoTax = 0;
//            for (ContainerBillFee bf2 : billFees) {
//                if (Utils.isNotEmpty(bf2.getPrice())) {
//                    if (bf2.getType().equals("应收")) {
//                        float cc = Float.parseFloat(bf2.getPrice());
//                        ysTotalNoTax += cc;
//                    } else {
//                        float cc = Float.parseFloat(bf2.getPrice());
//                        yfTotalNoTax += cc;
//                    }
//                }
//            }
//            row.add(Utils.twoDot(ysTotalNoTax));
//            row.add(Utils.twoDot(yfTotalNoTax));
//            int profitIndex = feeNamesys.size() + feeNamesyf.size() + 6;
//            row.add("function::TRUNC(IMSUB(IMSUB(IMSUB(" + letters[profitIndex + 3] + (i + 6) + "," + letters[profitIndex] + (i + 6) + ")," + letters[profitIndex + 1] + (i + 6) + ")," + letters[profitIndex + 2] + (i + 6) + "),2)");
//            row.add("10");
////            row.add(Utils.twoDot(ysTotalNoTax-yfTotalNoTax-10)+"");
//            profitIndex = feeNamesys.size() + feeNamesyf.size() + 10;
//            row.add("function::TRUNC(IMSUB(IMSUB(" + letters[profitIndex] + (i + 6) + "," + letters[profitIndex + 1] + (i + 6) + ")," + letters[profitIndex + 3] + (i + 6) + "),2)");
//            row.add("");
//            String numColumns = ",";
//            for (int t = 0; t < feeNamesys.size() + feeNamesys.size() + 8; t++) {
//                numColumns += (t + 18) + ",";
//            }
//            setRow(xssfRow, row, cellStyle, numColumns);
//        }
//
//        // 合并单元格
//        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 23 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
//        xssfSheet.addMergedRegion(cra);
//        cra = new CellRangeAddress(1, 1, 0, 23 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
//        xssfSheet.addMergedRegion(cra);
//        cra = new CellRangeAddress(2, 2, 0, 23 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
//        xssfSheet.addMergedRegion(cra);
//        cra = new CellRangeAddress(3, 3, 18, 17 + feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
//        xssfSheet.addMergedRegion(cra);
//        cra = new CellRangeAddress(3, 3, 19 + feeNamesys.size(), 18 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
//        xssfSheet.addMergedRegion(cra);
//        for (int i = 0; i <= 25 + feeNamesys.size() + feeNamesyf.size(); i++) {
//            if (i < 18 || i == (18 + feeNamesys.size()) || i > (18 + feeNamesys.size() + feeNamesyf.size())) {
//                cra = new CellRangeAddress(3, 4, i, i); // 起始行, 终止行, 起始列, 终止列
//                xssfSheet.addMergedRegion(cra);
//                RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
//                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
//                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
//                RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
//            }
//        }
//
//        xssfRow = xssfSheet.createRow(5 + containerBillList.size());
//        //创建每个单元格Cell，即列的数据
//        xssfCell = xssfRow.createCell(0); //创建单元格
//        fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 10); // 大小
//        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        cellStyle.setBorderBottom(BorderStyle.THIN);
//        cellStyle.setBorderLeft(BorderStyle.THIN);
//        cellStyle.setBorderRight(BorderStyle.THIN);
//        cellStyle.setBorderTop(BorderStyle.THIN);
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("合计:"); //设置单元格内容
//
//        for (int i = 0; i <= feeNamesys.size() + feeNamesyf.size() + 6; i++) {
//            xssfCell = xssfRow.createCell(18 + i); //创建单元格
//            fontStyle = xssfWorkbook.createFont(); // 字体样式
//            fontStyle.setFontName("宋体"); // 字体
//            fontStyle.setFontHeightInPoints((short) 10); // 大小
//            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//            cellStyle.setAlignment(HorizontalAlignment.CENTER);
//            // 将字体样式添加到单元格样式中
//            cellStyle.setFont(fontStyle);
//            cellStyle.setBorderBottom(BorderStyle.THIN);
//            cellStyle.setBorderLeft(BorderStyle.THIN);
//            cellStyle.setBorderRight(BorderStyle.THIN);
//            cellStyle.setBorderTop(BorderStyle.THIN);
//            xssfCell.setCellStyle(cellStyle);
//            xssfCell.setCellType(CellType.NUMERIC);
//            xssfCell.setCellFormula("SUM(" + letters[i + 8] + "6:" + letters[i + 8] + (containerBillList.size() + 5) + ")"); //设置单元格内容
//        }
//        //创建每个单元格Cell，即列的数据
//        xssfCell = xssfRow.createCell(20 + feeNamesys.size() + feeNamesyf.size() + 5); //创建单元格
//        fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 10); // 大小
//        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        cellStyle.setBorderBottom(BorderStyle.THIN);
//        cellStyle.setBorderLeft(BorderStyle.THIN);
//        cellStyle.setBorderRight(BorderStyle.THIN);
//        cellStyle.setBorderTop(BorderStyle.THIN);
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue(""); //设置单元格内容
//
//        cra = new CellRangeAddress(5 + containerBillList.size(), 5 + containerBillList.size(), 0, 17); // 起始行, 终止行, 起始列, 终止列
//        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
//        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
//        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
//        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
//        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File(getUploadDir() + fileName));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();
    }


    public void setRow(XSSFRow xssfRow, List<String> row, XSSFCellStyle cellStyle, String changeNum) {
        //创建每个单元格Cell，即列的数据
        if (Utils.isNotEmpty(row)) {
            for (int i = 0; i < row.size(); i++) {
                XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
                String str = row.get(i);
                if (str == null) {
                    str = "";
                }
                //https://blog.csdn.net/weixin_29100399/article/details/114203311
                if (str.indexOf("function::") != -1) {
                    xssfCell.setCellFormula(str.substring(10, str.length())); //设置单元格内容
                } else if (changeNum.indexOf("," + i + ",") != -1) {
                    xssfCell.setCellType(CellType.NUMERIC);
                    if (Utils.isNotEmpty(row.get(i))) {
                        xssfCell.setCellValue(Double.valueOf(row.get(i))); //设置单元格内容
                    } else {
                        xssfCell.setCellValue(0);
                    }
                } else {
                    xssfCell.setCellValue(row.get(i)); //设置单元格内容
                }
                if (cellStyle != null) {
                    xssfCell.setCellStyle(cellStyle);
                }
            }
        }
    }
    @Resource
    private ConfigProperties config;

    /**
     * 文件上传基目录
     */
    private String getUploadBaseDir() {
        return File.listRoots()[config.getUploadLocation()].getAbsolutePath()
                .replace("\\", "/") + "/upload/";
    }

    /**
     * 文件上传位置
     */
    private String getUploadDir() {
        return getUploadBaseDir() + "file/";
    }

}
